#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto

${HIVE_HOME} -S -e "
-- 建库
-- 创建DWS层
Drop DataBase If Exists zx_dws;
Create DataBase If Not Exists zx_dws;

-- 切换库
Use zx_dws;

Drop Table If Exists zx_dws.dws_attendance_stepcount;
Create Table If Not Exists zx_dws.dws_attendance_stepcount(
    class_id                  String           Comment '班级id',
    dt                        String           Comment '日期',
    step_type                 String           Comment '时间阶段',
    all_attendance_count      Bigint           Comment '总出勤人数',
    all_attendance_ratio      Decimal(38, 4)   Comment '总出勤率',
    normal_attendance_count   Bigint           Comment '正常出勤人数',
    normal_attendance_ratio   Decimal(38, 4)   Comment '正常出勤率',
    late_count                Bigint           Comment '迟到人数',
    late_ratio                Decimal(38, 4)   Comment '迟到率',
    leave_cnt                 Bigint           Comment '请假人数',
    leave_ratio               Decimal(38, 4)   Comment '请假率',
    truancy_count             Bigint           Comment '旷课人数',
    truancy_ratio             Decimal(38, 4)   Comment '旷课率',
    stu_totals                Bigint           Comment '班级总人数'
) comment '考勤主题时间分段统计表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

"

wait 3

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

Use hive.zx_dws;

-- 指标计算
-- todo 每天每个班级各时间段的出勤人数、出勤率
-- todo 每天各个班级各时间段的迟到人数、迟到率
Insert Into hive.zx_dws.dws_attendance_stepcount
With signin_tmp As (
    -- todo Step1：根据打卡时间和上课作息，打标记，区分打卡时间属于哪个时间段 --> 记作 打卡临时表 signin_tmp
    --              总出勤打卡时间区间为： 上课前40分钟~下课
    Select
        class_date,
        class_id,
        student_id,
        signin_time,
        morning_begin_time,
        morning_end_time,
        afternoon_begin_time,
        afternoon_end_time,
        evening_begin_time,
        evening_end_time,
        Case When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', morning_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', morning_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'morning'
             When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', afternoon_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'afternoon'
             When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', evening_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', evening_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'evening'
             Else 'other'
             End As step_type_all,
        signin_date,
        content
    From hive.zx_dwb.dwb_signin_wide
    )
, signin_row_number As (
    -- todo Step2：筛选掉不是在上课时间内的打卡信息
    --              并且根据打卡时间进行排序，如果有多次打卡，只取上课时间内最早的一次打卡记录 --> 记作 打卡排序表 signin_row_number
    Select
       class_date,
       class_id,
       student_id,
       signin_time,
       morning_begin_time,
       morning_end_time,
       afternoon_begin_time,
       afternoon_end_time,
       evening_begin_time,
       evening_end_time,
       step_type_all,
       Row_Number() Over(Partition By class_date, class_id, student_id, step_type_all Order By signin_time) As signin_time_all_rn
    From signin_tmp
    Where step_type_all <> 'other'
    )
, signin_public As (
    -- todo Step3：根据排序结果，取最早的一次打卡记录
    --              同时排除了一个人既在正常打卡时间内打卡，又在迟到时间内打卡的异常记录
    --              该表可以重复用于计算正常打卡和迟到打卡，因此 --> 记作 打卡公共表 signin_public
    Select
        class_date,
        class_id,
        student_id,
        signin_time,
        morning_begin_time,
        morning_end_time,
        afternoon_begin_time,
        afternoon_end_time,
        evening_begin_time,
        evening_end_time,
        step_type_all
    From signin_row_number
    Where signin_time_all_rn = 1
    )
, signin_all_count As (
    -- todo Step4：根据日期、班级、时间段，三个维度进行分组
    --              计算学生打卡人数，即为总出勤人数 --> 记作 打卡总数表 signin_all_count
    Select
        class_date,
        class_id,
        step_type_all,
        Count(student_id) As signin_all_cnt
    From signin_public
    Group By class_date, class_id, step_type_all
    )
, signin_nor As (
    -- todo Step5：同理，根据打卡时间和班级作息时间，打标记，区分打卡时间属于哪个时间段 --> 记作 正常打卡表 signin_nor
    --              正常打卡时间区间为： 上课前40分钟~上课后10分钟
    Select
       class_date,
       class_id,
       student_id,
       signin_time,
       Case When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', morning_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_add('minute', 10, Date_Parse(Concat(class_date,' ', morning_begin_time), '%Y-%m-%d %H:%i:%s'))
                Then 'morning'
            When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_add('minute', 10, Date_Parse(Concat(class_date,' ', afternoon_begin_time), '%Y-%m-%d %H:%i:%s'))
                Then 'afternoon'
            When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', -40, Date_Parse(Concat(class_date,' ', evening_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_add('minute', 10, Date_Parse(Concat(class_date,' ', evening_begin_time), '%Y-%m-%d %H:%i:%s'))
                Then 'evening'
            Else 'other'
            End As step_type_nor
    From signin_public
    )
, signin_nor_count As (
    -- todo Step6：根据日期、班级、时间段，三个维度进行分组
    --              计算学生正常打卡人数，即为正常出勤人数 --> 记作 正常打卡人数表 signin_nor_count
    Select
        class_date,
        class_id,
        step_type_nor,
        Count(student_id) As signin_nor_cnt
    From signin_nor
    Where step_type_nor <> 'other'
    Group By class_date, class_id, step_type_nor
    )
, signin_later As (
    -- todo Step7：同理，根据打卡时间和班级作息时间，打标记，区分打卡时间属于哪个时间段 --> 记作 迟到打卡表 signin_later
    --              迟到打卡时间区间为： 上课后10分钟~下课
    Select
       class_date,
       class_id,
       student_id,
       signin_time,
       Case When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', 10, Date_Parse(Concat(class_date,' ', morning_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', morning_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'morning'
             When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', 10, Date_Parse(Concat(class_date,' ', afternoon_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', afternoon_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'afternoon'
             When Date_Parse(signin_time, '%Y-%m-%d %H:%i:%s') Between Date_add('minute', 10, Date_Parse(Concat(class_date,' ', evening_begin_time), '%Y-%m-%d %H:%i:%s')) And Date_Parse(Concat(class_date,' ', evening_end_time), '%Y-%m-%d %H:%i:%s')
                Then 'evening'
             Else 'other'
             End As step_type_later
    From signin_public
    )
, signin_later_count As (
    -- todo Step8：根据日期、班级、时间段，三个维度进行分组
    --              计算学生迟到打卡人数，即为迟到出勤人数 --> 记作 迟到打卡人数表 signin_later_count
    Select
        class_date,
        class_id,
        step_type_later,
        Count(student_id) As signin_later_cnt
    From signin_later
    Where step_type_later <> 'other'
    Group By class_date, class_id, step_type_later
    )
, attendance_count As (
    -- todo Step9：把打卡总数表 signin_all_count、正常打卡人数表 signin_nor_count、迟到打卡人数表 signin_later_count进行拼接，合并成一张总表
--                  这里计算了全部的数据，也可以只计算总出勤人数和（正常出勤|迟到出勤）人数，之后通过二者相减，获得（迟到打卡|正常打卡）人数
--                  同时保证每张表的维度相等，并且对人数空值进行转换  --> 记作打卡总表 attendance_count
    Select
        all_cnt.class_date,
        all_cnt.class_id,
        all_cnt.step_type_all,
        Coalesce(all_cnt.signin_all_cnt, 0) As signin_all_cnt,
        Coalesce(nor_cnt.signin_nor_cnt, 0) As signin_nor_cnt,
        Coalesce(later_cnt.signin_later_cnt, 0) As signin_later_cnt
    From signin_all_count all_cnt
    Left Join signin_nor_count nor_cnt On all_cnt.class_date = nor_cnt.class_date
                                            And all_cnt.class_id = nor_cnt.class_id
                                            And all_cnt.step_type_all = nor_cnt.step_type_nor
    Left Join signin_later_count later_cnt On all_cnt.class_date = later_cnt.class_date
                                            And all_cnt.class_id = later_cnt.class_id
                                            And all_cnt.step_type_all = later_cnt.step_type_later
    )

-- todo 每天各个班级各时间段的请假人数、请假率
-- todo 每天各个班级各时间段的旷课人数、旷课率

, leave_apply_tmp As (
    -- todo Step10：根据各个上课时间是否处于学生请假时间区间内，进行标记，请假为1，否则为0 --> 记作 请假临时表 leave_apply_tmp
    --              由于人数处于同一行，无法进行统一的维度计算，所以需要进行列转行操作，这里使用Union All方法
    Select
        class_date,
        class_id,
        begin_time,
        end_time,
        morning_begin_time,
        morning_end_time,
        afternoon_begin_time,
        afternoon_end_time,
        evening_begin_time,
        evening_end_time,
        Case When Concat(class_date, ' ', morning_begin_time) Between begin_time And end_time Then 1 Else 0 End As morning,
        Case When Concat(class_date, ' ', afternoon_begin_time) Between begin_time And end_time Then 1 Else 0 End As afternoon,
        Case When Concat(class_date, ' ', evening_begin_time) Between begin_time And end_time Then 1 Else 0 End As evening
    From hive.zx_dwb.dwb_leave_apply_wide
    Where class_date Is Not Null
    )
, leave_count As (
    -- todo Step11：对上课日期和班级进行分组，分别进行各个时间段，对标记的求和，计算出不同时间段的请假人数 --> 记作 请假总表 leave_count
    Select
        class_date,
        class_id,
        'morning' As step_type,
        Sum(morning) As leave_cnt
    From leave_apply_tmp
    Group By class_date, class_id
    Union All
    Select
        class_date,
        class_id,
        'afternoon' As step_type,
        Sum(afternoon) As leave_cnt
    From leave_apply_tmp
    Group By class_date, class_id
    Union All
    Select
        class_date,
        class_id,
        'evening' As step_type,
        Sum(evening) As leave_cnt
    From leave_apply_tmp
    Group By class_date, class_id
    )
, student_count As (
    -- todo Step12：拉取在读学员人数信息表，获取各个班级的总人数 --> 记作 班级人数总表 student_count
    Select
       class_id,
       studying_student_count,
       studying_date
    From hive.zx_dwd.fact_class_studying_student_count
    )
-- todo Step13:将打卡总表 attendance_count、请假总表 leave_count、班级人数总表 student_count，进行关联合并
-- todo Step14：分别查询 总出勤人数 all_attendance_count、总出勤率 all_attendance_ratio
--                      正常出勤人数 normal_attendance_count、正常出勤率 normal_attendance_ratio
--                      迟到人数 late_count、迟到率 late_ratio
--                      请假人数 leave_count、请假率 leave_ratio
--                      旷课人数 truancy_count、旷课率 truancy_ratio
--              需要注意数据的Null值处理!!!
Select

    --  todo 这里是上卷计算，包括时间阶段粒度
    Cast(att_cnt.class_id As Varchar) As class_id,  -- 班级id
    att_cnt.class_date As dt,  -- 日期
    Coalesce(step_type_all, 'All') As step_type,  -- 时间阶段
    Sum(signin_all_cnt) As all_attendance_count,  -- 总出勤人数
    Avg(signin_all_cnt * 1.0000 / studying_student_count) As all_attendance_ratio,  -- 总出勤率
    Sum(signin_nor_cnt) As normal_attendance_count,  -- 正常出勤人数
    Avg(signin_nor_cnt * 1.0000 / studying_student_count) As normal_attendance_ratio,  -- 正常出勤率
    Sum(signin_later_cnt) As late_count,  -- 迟到人数
    Avg(signin_later_cnt * 1.0000 / studying_student_count) As late_ratio,  -- 迟到率

    -- todo 这里要考虑到某天某班级没有学生请假的情况，如果没有人请假，请假总表就不会有计算结果，导致左连接后，请假表内数据为Null
    Sum(Coalesce(leave_cnt, 0)) As leave_cnt,  -- 请假人数
    Avg(Coalesce(leave_cnt, 0) * 1.0000 / studying_student_count) As leave_ratio,  -- 请假率

    -- todo 这里的请假人数依然要进行空值处理，原因和计算请假人数时一样，如果为Null，则总体计算结果为空，导致数据不准确
    Sum(Coalesce(studying_student_count - signin_all_cnt - Coalesce(leave_cnt, 0), 0)) As truancy_count,  -- 旷课人数
    Avg(Coalesce(studying_student_count - signin_all_cnt - Coalesce(leave_cnt, 0), 0) * 1.0000 / studying_student_count) As truancy_ratio,  -- 旷课率
    Sum(stu_cnt.studying_student_count) As stu_totals  -- 班级总人数


From attendance_count att_cnt
Left Join leave_count lea_cnt On att_cnt.class_date = lea_cnt.class_date
                                And att_cnt.class_id = lea_cnt.class_id
                                And att_cnt.step_type_all = lea_cnt.step_type
Left Join student_count stu_cnt On att_cnt.class_date = stu_cnt.studying_date
                                    And att_cnt.class_id = stu_cnt.class_id

    --  todo 这里是日阶段计算和上卷计算，使用的分组计算方式
Group By
Grouping Sets ((att_cnt.class_date, att_cnt.class_id, step_type_all), (att_cnt.class_date, att_cnt.class_id));

"

